package com.mentalhealthplatform.exception;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.MessageSource;
import org.springframework.context.i18n.LocaleContextHolder;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.dao.OptimisticLockingFailureException;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.ControllerAdvice;
import org.springframework.web.bind.annotation.ExceptionHandler;

import java.time.LocalDateTime;
import java.util.Locale;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@ControllerAdvice
public class DatabaseExceptionHandler {

    private static final Logger logger = LoggerFactory.getLogger(DatabaseExceptionHandler.class);
    private final MessageSource messageSource;

    @Autowired
    public DatabaseExceptionHandler(MessageSource messageSource) {
        this.messageSource = messageSource;
    }

    @ExceptionHandler(DataAccessException.class)
    public ResponseEntity<ErrorResponse> handleDatabaseException(DataAccessException e) {
        logger.error("Database exception occurred");

        LocalDateTime timestamp = LocalDateTime.now();
        Locale locale = LocaleContextHolder.getLocale();

        // 根据异常类型分发到不同的处理方法
        if (e instanceof DataIntegrityViolationException) {
            return handleDataIntegrityViolationException((DataIntegrityViolationException) e, timestamp, locale);
        } else if (e instanceof DuplicateKeyException) {
            return buildErrorResponse("error.uniqueConstraint", e.getMessage(), HttpStatus.BAD_REQUEST, timestamp, locale);
        } else if (e instanceof OptimisticLockingFailureException) {
            return buildErrorResponse("error.optimisticLockingFailure", e.getMessage(), HttpStatus.CONFLICT, timestamp, locale);
        } else {
            return buildErrorResponse("error.databaseOperationFailed", e.getMessage(), HttpStatus.INTERNAL_SERVER_ERROR, timestamp, locale);
        }
    }

    private ResponseEntity<ErrorResponse> handleDataIntegrityViolationException(DataIntegrityViolationException e, LocalDateTime timestamp, Locale locale) {
        String message = e.getMostSpecificCause().getMessage();

        if (isForeignKeyViolation(message)) {
            String fkDetails = extractForeignKeyDetails(message, locale);
            logger.error("Foreign key violation: {}", fkDetails);
            return buildErrorResponse("error.foreignKeyConstraint", fkDetails, HttpStatus.CONFLICT, timestamp, locale);
        } else if (isDuplicateKeyViolation(message)) {
            logger.error("Duplicate key violation: {}", message);
            return buildErrorResponse("error.uniqueConstraint", e.getMessage(), HttpStatus.BAD_REQUEST, timestamp, locale);
        } else {
            logger.error("Data integrity violation: {}", message);
            return buildErrorResponse("error.dataIntegrity", e.getMessage(), HttpStatus.BAD_REQUEST, timestamp, locale);
        }
    }

    private boolean isForeignKeyViolation(String message) {
        return message != null && message.toLowerCase().contains("foreign key");
    }

    private boolean isDuplicateKeyViolation(String message) {
        return message != null && message.toLowerCase().contains("duplicate");
    }

    private String extractForeignKeyDetails(String message, Locale locale) {
        String regex = "(foreign key\\s*\\(`([^`]+)`\\)\\s*references\\s*`([^`]+)`\\s*\\(`([^`]+)`\\))";
        Pattern pattern = Pattern.compile(regex, Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(message);

        if (matcher.find()) {
            String columnName = matcher.group(2);
            String referencedTable = matcher.group(3);
            String referencedColumn = matcher.group(4);
            return String.format(getMessage("error.foreignKeyDetail", locale), columnName, referencedTable, referencedColumn);
        } else {
            logger.warn("Failed to extract foreign key details from message: {}", message);
            return getMessage("error.foreignKeyDetailUnknown", locale);
        }
    }

    private ResponseEntity<ErrorResponse> buildErrorResponse(String messageKey, String detail, HttpStatus status, LocalDateTime timestamp, Locale locale) {
        String message = getMessage(messageKey, locale);
        ErrorResponse response = new ErrorResponse(message, detail, status.value(), timestamp);
        return new ResponseEntity<>(response, status);
    }

    private String getMessage(String key, Locale locale) {
        return messageSource.getMessage(key, null, locale);
    }
}
